package com.zzx.dao.imql;

import com.zzx.Util.DruidUtil;
import com.zzx.bean.Courier;
import com.zzx.dao.BaseCourierDao;
import com.zzx.exception.DuplicateCodeException;
import com.zzx.exception.DuplicateIdentityException;
import org.apache.http.client.utils.DateUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Spider Man
 * @Description:
 * @Date Created in 2020-09-15 13:34
 * @Modified By:
 */
public class CourierDaoImp implements BaseCourierDao {
    private final String SQL_CONSOLE = "SELECT COUNT(cid) size,COUNT(TO_DAYS(cregistertime) =TO_DAYS(NOW()) OR NULL) day FROM courier";
    private final String SQL_FIND_ALL_COURIER_INFO = "SELECT * FROM courier";
    private final String SQL_FIND_ALL_COURIER_INFO_LIMIT = "SELECT * FROM courier LIMIT ?,?";
    private final String SQL_FIND_COURIER_BY_CPHONE = "SELECT * FROM courier WHERE cPhone=?;";
    private final String SQL_INSERT = "INSERT courier (cName,cPhone,cIdentity,cPassword,cRegisterTime) VALUES(?,?,?,?,NOW())";
    private final String SQL_UPDATE = "UPDATE courier SET cName=?,cPhone=?,cIdentity=?,cPassword=? WHERE cid=?";
    private final String SQL_DELETE = "DELETE from courier where cid=?";

    /**
     * 用于显示用户人数和用户日注册量
     *
     * @return {size:总数，day:日增加量}
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int size = resultSet.getInt("size");
                int day = resultSet.getInt("day");
                Map<String, Integer> map = new HashMap<>();
                map.put("size",size);
                map.put("day",day);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 用于查询所有快递员的信息
     *
     * @param limit      是否分页的标记，true表示要分页，false表示不用分页
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递对象员的集合
     */
    @Override
    public List<Courier> findAllCourierInfo(boolean limit, int offset, int pageNumber) {
        List<Courier> list = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            if (limit) {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_COURIER_INFO_LIMIT);
                // 3.参数填充
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, pageNumber);
            } else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL_COURIER_INFO);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Integer cid = resultSet.getInt("cid");
                String cName = resultSet.getString("cName");
                String cPhone = resultSet.getString("cPhone");
                String cIdentity = resultSet.getString("cIdentity");
                String cPassword = resultSet.getString("cPassword");
                String cDispatch = resultSet.getString("cDispatch");
                Timestamp cRegisterTime = resultSet.getTimestamp("cRegisterTime");
                Timestamp cLoginTime = resultSet.getTimestamp("cLoginTime");
                Courier courier = new Courier(cid,cName,cPhone,cIdentity,cPassword,cDispatch,cRegisterTime,cLoginTime);
                list.add(courier);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 根据手机号查找快递员
     *
     * @param cPhone 要查找的快递员的手机号
     * @return 快递员的信息
     */
    @Override
    public Courier findCourierByCPhone(String cPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_COURIER_BY_CPHONE);
            preparedStatement.setString(1,cPhone);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Integer cid = resultSet.getInt("cid");
                String cName = resultSet.getString("cName");
                String cIdentity = resultSet.getString("cIdentity");
                String cPassword = resultSet.getString("cPassword");
                String cDispatch = resultSet.getString("cDispatch");
                Timestamp cRegisterTime = resultSet.getTimestamp("cRegisterTime");
                Timestamp cLoginTime = resultSet.getTimestamp("cLoginTime");
                Courier courier = new Courier(cid,cName,cPhone,cIdentity,cPassword,cDispatch,cRegisterTime,cLoginTime);
                return courier;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 插入快递员信息
     *
     * @param courier 插入快递员信息
     * @return true表示成功, false表示失败
     */
    @Override
    public boolean insert(Courier courier) throws DuplicateIdentityException {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            preparedStatement.setString(1,courier.getcName());
            preparedStatement.setString(2,courier.getcPhone());
            preparedStatement.setString(3,courier.getcIdentity());
            preparedStatement.setString(4,courier.getcPassword());
            return preparedStatement.executeUpdate()>0;
        } catch (SQLException throwables) {
            if (throwables.getMessage().endsWith("for key 'cidentity'")) {
                DuplicateIdentityException duplicateIdentityException = new DuplicateIdentityException(throwables.getMessage());
                throw duplicateIdentityException;
            } else {
                throwables.printStackTrace();
            }
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }

    /**
     * 快递员的修改
     *
     * @param cid     快递员的ID
     * @param courier 新的快递员对象
     * @return ture表示更新成功，false表示更新失败
     */
    @Override
    public boolean update(int cid, Courier courier) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1,courier.getcName());
            preparedStatement.setString(2,courier.getcPhone());
            preparedStatement.setString(3,courier.getcIdentity());
            preparedStatement.setString(4,courier.getcPassword());
            preparedStatement.setInt(5,cid);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }

    /**
     * 根据ID删除快递员信息
     *
     * @param cid 要删除的快递ID
     * @return true表示成功，false表示删除失败
     */
    @Override
    public boolean delete(int cid) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setInt(1,cid);
            return preparedStatement.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.closeConnectionAndStatement(connection,preparedStatement);
        }
        return false;
    }
}
